package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaRMParams;
import com.mbc.rmt.data.AgentCashTransactionDataList;
import com.mbc.rmt.entities.AgentCashTransaction;
import com.mbc.rmt.entities.AgentCashTransactionRpt;

public class AgentCashTransactionDAOImpls extends DAO<AgentCashTransaction> implements AgentCashTransactionDAO{

	public AgentCashTransactionDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "agentcashtransaction";
	}
	public final String field_transaction_type = "transactiontype";
	@Override
	public void create(AgentCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "insert into "
				+ table_name
				+ " ( InvoiceId, InvoiceNO, InvoiceDate, DefaultBranchID, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, AgentId, AgentCode, CurrencyId, Rate, PaidAmount, BankId, BankCode, TransactionType) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getInvoice_Id());
		pstmt.setString(i++, entity.getInvoiceNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getInvoiceDate().getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getAgentId());
		pstmt.setString(i++, entity.getAgentCode());
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getPaidAmount());
		if(entity.getBankId()!=null)
			pstmt.setLong(i++, entity.getBankId());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.setInt(i++, entity.getTransactionType());
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	@Override
	public void update(AgentCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		query = "update "
				+ table_name
			    + " set InvoiceId = ?, InvoiceNO = ?, InvoiceDate = ?, DefaultBranchID = ?, CreatedBy = ?, ModifiedBy = ?," +
				" CreatedDate = ?, ModifiedDate = ?, AgentId = ?, AgentCode = ?, CurrencyID = ?, Rate = ?, paidAmount = ?, BankID = ?, BankCode = ?,TransactionType = ?"
				+ " where InvoiceId = " + entity.getInvoice_Id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getInvoice_Id());
		pstmt.setString(i++, entity.getInvoiceNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getInvoiceDate().getTime()));
		pstmt.setLong(i++, entity.getDefaultBranchId());
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getAgentId());
		pstmt.setString(i++, entity.getAgentCode());
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getPaidAmount());
		if(entity.getBankId()!=null)
			pstmt.setLong(i++, entity.getBankId());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getBankcode());
		pstmt.setLong(i++, entity.getTransactionType());
		
		pstmt.executeUpdate();
		pstmt.close();
	}
	@Override
	public void delete(AgentCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		//query = getDeleteQuery() + " where invoiceId = " + key;
		query = "DELETE FROM "+table_name+" WHERE invoiceId = "+entity.getInvoice_Id()+" AND transactiontype = "+entity.getTransactionType();
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}
	@Override
	protected AgentCashTransaction readRecord(ResultSet rs) throws SQLException {
		// TODO Auto-generated method stub
		AgentCashTransaction l_entity = new AgentCashTransaction();
		l_entity.setInvoice_Id(rs.getLong("InvoiceId"));
		l_entity.setInvoiceNo(rs.getString("InvoiceNO"));
		l_entity.setInvoiceDate(rs.getDate("InvoiceDate"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setAgentId(rs.getLong("AgentId"));
		l_entity.setCurrencyId(rs.getLong("CurrencyID"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setPaidAmount(rs.getDouble("PaidAmount"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setTransactionType(rs.getInt("TransactionType"));
		return l_entity;
	}
	@Override
	public AgentCashTransaction find(long key) throws SQLException {
		// TODO Auto-generated method stub
		AgentCashTransaction l_AgentCashTran = null;
		query = getSelectQuery() + " where invoiceid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AgentCashTran = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_AgentCashTran;
	}
	@Override
	public AgentCashTransactionDataList findAgentCashTranDataList(
			CriteriaRMParams cri) throws SQLException {
		// TODO Auto-generated method stub
		AgentCashTransactionDataList l_DataList = new AgentCashTransactionDataList();
		ArrayList<AgentCashTransaction> l_entityList = new ArrayList<AgentCashTransaction>();
		String l_cri = ""//" and (InvoiceNo like N'%" + cri.getKeyword() + "%')"
				+ " and  act.DefaultBranchID =" + cri.getBranchId() +" and act.TransactionType ="+cri.getStatus();
				
		if(cri.getFieldname().equals("InvoiceDate"))
			l_cri += "and Convert(varchar(100), act." + cri.getFieldname() + ", 103) like N'%" + cri.getKeyword() + "%'";
		else
			l_cri += "and act." + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
		
			query = "DECLARE @homecurrencyid bigint"+
					" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
					" set rowcount "+ cri.getSize() + 
					" SELECT * FROM (" +
					" SELECT row_number()OVER(ORDER BY  act.InvoiceId desc)rowno," +
					" act.InvoiceId, act.InvoiceNO,act.InvoiceDate,act.agentid," +
					" act.agentcode,ag.name, act.currencyid,c.currencycode," +
					" ISNULL(cr.currencyrateid,0) currencyrateid," +
					" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=act.currencyid" +
					" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
					" act.rate,(act.PaidAmount +(SELECT CurrentBalance FROM dbo.AgentBalance WHERE agent_id = act.agentid" +
					" AND currencyid = act.currencyid))TotalAmount, act.PaidAmount," +
					" (SELECT CurrentBalance FROM dbo.AgentBalance WHERE agent_id = act.agentid AND currencyid = act.currencyid)Balance, act.bankid," +
					" ISNULL(act.bankcode,'') bankcode,act.TransactionType" +
					" from agentcashtransaction act " +
					" LEFT JOIN Agent ag ON ag.agent_id = act.agentid " +
					" LEFT JOIN bank bk ON act.bankid = bk.bankid " +
					" INNER JOIN Currency c ON act.currencyid=c.currencyid " +
					" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = act.currencyid " +
					" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid = "+cri.getBranchId()+ 
					" where 1 = 1 " + l_cri + ") agentcashtransaction " +
					" where rowno > " + cri.getIndex();  
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = " and (invoiceNo like N'%" + cri.getKeyword()
				+ "%')" + " and DefaultBranchID = " + cri.getBranchId()
				+" and "+field_transaction_type +"="+cri.getStatus();
		query = "select count(Invoiceid) from " + table_name + " where 1 = 1"
				+ l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}
	protected AgentCashTransaction readRecords(ResultSet rs) throws SQLException {
		AgentCashTransaction l_entity = new AgentCashTransaction();
		l_entity.setInvoice_Id(rs.getLong("InvoiceId"));
		l_entity.setInvoiceNo(rs.getString("InvoiceNO"));
		l_entity.setInvoiceDate(rs.getDate("InvoiceDate"));
		l_entity.setAgentId(rs.getLong("AgentId"));
		l_entity.setAgentCode(rs.getString("AgentCode"));
		l_entity.setAgent_name(rs.getString("name"));
		l_entity.setCurrencyId(rs.getLong("CurrencyID"));
		l_entity.setCurrency_Code(rs.getString("currencycode"));
		l_entity.setCurrency_rate_Code(rs.getString("currencyratecode"));
		l_entity.setCurrency_rate_Id(rs.getLong("CurrencyRateId"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setPaidAmount(rs.getDouble("PaidAmount"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setBankcode(rs.getString("bankcode"));
		l_entity.setBalance(rs.getDouble("Balance"));
		l_entity.setTransactionType(rs.getInt("TransactionType"));
		
		return l_entity;
	}
	@Override
	public AgentCashTransaction getAgentCashTransaction(
			AgentCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		AgentCashTransaction l_AgentCashTran = null;
		
		query = "DECLARE @homecurrencyid bigint"+
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)" +
				" select act.InvoiceId, act.InvoiceNO,act.InvoiceDate,act.agentid," +
				" act.agentcode,ag.name, act.currencyid,c.currencycode," +
				" ISNULL(cr.currencyrateid,0) currencyrateid," +
				" (CASE WHEN cr.currencyratecode IS NULL AND @homecurrencyid=act.currencyid" +
				" THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currencyratecode," +
				" act.rate,(act.PaidAmount +(SELECT CurrentBalance FROM dbo.AgentBalance WHERE agent_id = act.agentid" +
				" AND currencyid = act.currencyid))TotalAmount, act.PaidAmount," +
				" (SELECT CurrentBalance FROM dbo.AgentBalance WHERE agent_id = act.agentid AND currencyid = act.currencyid)Balance, act.bankid," +
				" ISNULL(act.bankcode,'') bankcode,act.TransactionType" +
				" from agentcashtransaction act " +
				" LEFT JOIN Agent ag ON ag.agent_id = act.agentid " +
				" LEFT JOIN bank bk ON act.bankid = bk.bankid " +
				" INNER JOIN Currency c ON act.currencyid=c.currencyid " +
				" LEFT JOIN CurrencyRate cr ON cr.SourceCurrencyid = act.currencyid " +
				" AND cr.convertcurrencyid=@homecurrencyid and cr.branchid ="+entity.getDefaultBranchId()+
				" where invoiceno = '"+entity.getInvoiceNo()+"' And act.transactiontype = " + entity.getTransactionType()+
				" and act.DefaultBranchId  = " + entity.getDefaultBranchId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_AgentCashTran = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_AgentCashTran;
	}
	@Override
	public ResultSet prepareAgentCashTransactionReport(AgentCashTransactionRpt info) throws SQLException {
		// TODO Auto-generated method stub
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_AgentCashTransactionRpt ?, ?, ?, ?, ?, ?, ? , ? , ? , ?, ?, ? , ? , ?,  ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AgentCashTransactionRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}
	
	private static void AgentCashTransactionRptParameter(CallableStatement l_pstmt,
			AgentCashTransactionRpt  info) throws SQLException {
		int i = 1;
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getFromDate().getTime()).toString());
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getToDate().getTime()).toString());
		l_pstmt.setString(i++, info.getFrominvoiceno());
		l_pstmt.setString(i++, info.getToinvoiceno());
		l_pstmt.setString(i++, info.getFromagentid());
		l_pstmt.setString(i++, info.getToagentid());
		l_pstmt.setString(i++, info.getFromtypeid());
		l_pstmt.setString(i++, info.getTotypeid());
		l_pstmt.setString(i++, info.getFromuserid());
		l_pstmt.setString(i++, info.getTouserid());
		l_pstmt.setString(i++, info.getFromcurrencyid());
		l_pstmt.setString(i++, info.getTocurrencyid());
		l_pstmt.setString(i++, info.getFrombankcheque());
		l_pstmt.setString(i++, info.getTobankcheque());
		l_pstmt.setInt(i++, info.getTranstatus());
		l_pstmt.setLong(i++, info.getBranchid());
		l_pstmt.setString(i++, info.getOrderby());
	}
	@Override
	public AgentCashTransaction getAgentbalance(AgentCashTransaction entity) throws SQLException {
		// TODO Auto-generated method stub
		AgentCashTransaction l_agent = new AgentCashTransaction();
		query = "SELECT a.agent_id,a.agent_code,a.name,ab.currentbalance FROM agent a"+
				" Inner JOIN agentbalance ab ON a.agent_id = ab.agent_id WHERE "+
				" agent_code = '"+entity.getAgentCode()+"' AND ab.currencyid = "+entity.getCurrencyId()+
				" and a.branchid = "+entity.getDefaultBranchId()+
				" and a.[status] = 0";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_agent = readRecordagent(rs);
		rs.close();
		pstmt.close();
		return l_agent;
    	}
	protected AgentCashTransaction readRecordagent(ResultSet rs) throws SQLException {
		AgentCashTransaction l_entity = new AgentCashTransaction();
		l_entity.setAgentId(rs.getLong("agent_id"));
		l_entity.setAgentCode(rs.getString("agent_code"));
		l_entity.setAgent_name(rs.getString("name"));
		l_entity.setBalance(rs.getDouble("currentbalance"));
		return l_entity;
	}
}
